from sqlalchemy import Column, Integer, String, Boolean, ForeignKey, Table,DateTime,Text
from sqlalchemy.orm import relationship
from datetime import datetime
from .database import Base

# 关联表 - 用户和角色的多对多关系
user_roles = Table('user_roles', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('role_id', Integer, ForeignKey('roles.id'), primary_key=True)
)

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True, nullable=False)
    password = Column(String, nullable=False)
    name = Column(String, nullable=False)
    phone = Column(String, nullable=False)
    category = Column(String, nullable=False)
    sec_type = Column(Integer, default=0)  # 二级权限：0-教务老师，1-顾问老师（仅当category=3时有效）
    desc = Column(String, default="")  # 简介（仅当category=3时使用）
    is_active = Column(Boolean, default=True)
    icon_path = Column(String)  # 图标路径
    
    # 关系
    roles = relationship("Role", secondary=user_roles, back_populates="users")

class Role(Base):
    __tablename__ = "roles"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, nullable=False)
    
    # 关系
    users = relationship("User", secondary=user_roles, back_populates="roles")
    
class File(Base):
    __tablename__ = "files"
    
    id = Column(Integer, primary_key=True, index=True)
    uuid = Column(String, unique=True, nullable=False)  # 文件唯一标识符
    filename = Column(String, nullable=False)  # 原始文件名
    file_path = Column(String, nullable=False)  # 文件存储路径
    saved_filename = Column(String, nullable=False)  # 保存的文件名
    file_size = Column(Integer, nullable=False)  # 文件大小（字节）
    file_type = Column(String)  # 文件类型
    display_name = Column(String)  # 显示名称，可编辑
    description = Column(String)  # 文件描述
    is_active = Column(Boolean, default=True)  # 是否可用
    watch_num =  Column(Integer) # 观看次数
    created_at = Column(DateTime, default=datetime.utcnow)  # 创建时间
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)  # 更新时间
    
    # 关系 - 上传者
    uploader_id = Column(Integer, ForeignKey("users.id"))
    uploader = relationship("User")

class Notice(Base):
    __tablename__ = "notices"
    
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)  # 标题
    pushOrg = Column(Text, nullable=False)  # 发布内容
    pushTime = Column(DateTime, nullable=False)  # 发布时间
    type = Column(Integer, nullable=False)  # 类型：0系统消息，1用户消息
    icon_path = Column(String)  # 图标路径
    link_url = Column(String)  # URL链接
    is_active = Column(Boolean, default=True)  # 是否可用
    created_at = Column(DateTime, default=datetime.utcnow)  # 创建时间
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)  # 更新时间
    
    # 关系 - 创建者
    creator_id = Column(Integer, ForeignKey("users.id"))
    creator = relationship("User")

class UserRelation(Base):
    __tablename__ = "user_relations"
    
    id = Column(Integer, primary_key=True, index=True)
    parent_phone = Column(String, nullable=False, index=True)  # 家长手机号
    student_phone = Column(String, nullable=False, index=True)  # 学生手机号
    created_at = Column(DateTime, default=datetime.utcnow)  # 创建时间
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)  # 更新时间
    
    # 关系
    # 与User表的关联关系可以通过手机号进行查询

class Course(Base):
    __tablename__ = "courses"
    
    id = Column(Integer, primary_key=True, index=True)
    cover = Column(String)  # 封面图片URL
    title = Column(String, nullable=False)  # 课程标题
    desc = Column(Text)  # 课程描述
    price = Column(Integer, nullable=False, default=0)  # 价格（单位：分）
    book_num = Column(Integer, default=0)  # 预订数量
    is_active = Column(Boolean, default=True)  # 是否可用
    created_at = Column(DateTime, default=datetime.utcnow)  # 创建时间
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)  # 更新时间
    
    # 关系 - 创建者
    creator_id = Column(Integer, ForeignKey("users.id"))
    creator = relationship("User")
    
    # 关系 - 课程章节
    categories = relationship("CourseCategory", back_populates="course", cascade="all, delete-orphan")

class CourseCategory(Base):
    __tablename__ = "course_categories"
    
    id = Column(Integer, primary_key=True, index=True)
    course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)  # 所属课程
    code = Column(String, nullable=False)  # 章节代码
    name = Column(String, nullable=False)  # 章节名称
    can_view = Column(Boolean, default=False)  # 是否可以查看
    content = Column(String)  # 内容描述（如：视频|25分钟45秒）
    url = Column(String)  # 视频或资源URL
    sort_order = Column(Integer, default=0)  # 排序顺序
    is_active = Column(Boolean, default=True)  # 是否可用
    created_at = Column(DateTime, default=datetime.utcnow)  # 创建时间
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)  # 更新时间
    
    # 关系 - 所属课程
    course = relationship("Course", back_populates="categories")

class Booking(Base):
    __tablename__ = "bookings"
    
    id = Column(Integer, primary_key=True, index=True)
    from_phone = Column(String, nullable=False, index=True)  # 预约者电话
    book_date = Column(String, nullable=False)  # 预约日期
    to_phone = Column(String, nullable=False, index=True)  # 预约对象电话
    notice_id = Column(Integer, ForeignKey("notices.id"))  # 关联的消息表id
    
    # 关系 - 关联的通知
    notice = relationship("Notice")


class Punch(Base):
    __tablename__ = "punches"
    
    id = Column(Integer, primary_key=True, index=True)
    phone = Column(String, nullable=False, index=True)  # 打卡者电话
    punch_date = Column(String, nullable=False)  # 打卡日期
    notice_id = Column(Integer, ForeignKey("notices.id"))  # 关联的消息表id
    type = Column(Integer, default=0)  # 打卡类型：0-单词打卡
    
    # 关系 - 关联的通知
    notice = relationship("Notice")


class Schedule(Base):
    __tablename__ = "schedules"
    
    id = Column(Integer, primary_key=True, index=True)
    phone = Column(String, nullable=False, index=True)  # 老师电话
    course_name = Column(String, nullable=False)  # 课程名称
    date = Column(String, nullable=False)  # 日期
    time_slot = Column(String, nullable=False)  # 时间段
    notice_id = Column(Integer, ForeignKey("notices.id"))  # 关联的消息表id

    # 关系 - 关联的通知
    notice = relationship("Notice")
